package DBLayer;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import ModelLayer.*;

public class DbSalesOrder implements IFDBSalesOrder {
	
	private Connection con;
	private DbCustomer customerDb;
	private DbSalesLine salesLineDb;	
	private DbDelivery deliveryDb;
	
	public DbSalesOrder()
	{
		con = DbConnection.getInstance().getDBcon();
		customerDb = new DbCustomer();
		salesLineDb = new DbSalesLine();
		deliveryDb = new DbDelivery();
	}

	@Override
	public ArrayList<SalesOrder> getAllSalesOrder() 
	{
		return miscWhere("");
	}
	
	public ArrayList<SalesOrder> getAllSalesOrder(String filter) {
		String wClause = " customerPhone LIKE '%" + filter + "%'";
		return miscWhere(wClause);
	}

	@Override
	public SalesOrder searchSalesOrder(int soId) 
	{
		String wClause = " id = '" + soId + "'";
		return singleWhere(wClause);
	}

	@Override
	public int insertSalesOrder(SalesOrder so) throws Exception 
	{
		int nextID = GetMax.getMaxId("Select max(id) from SalesOrder");
        nextID = nextID + 1;
        System.out.println("next id = " +  nextID);
		int rc = -1;
		String query="INSERT INTO SalesOrder(id, creationDate, paymentDate, total, custId, deliveryId, status, discount)  VALUES('"+
				nextID  + "','"  +
				"20140101"  + "','"  +
				"20140101" + "','" +
				so.getAmount() + "','" +
				so.getC().getId() + "','" +
				so.getD().getDeliveryId() + "','" +
				so.getStatus() + "','" +
				so.getDiscount() + "')";

		System.out.println("insert : " + query);
		try{ // insert new supplier
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
			
			insertSalesList(so.getSl(), nextID);
		}//end try
		catch(SQLException ex){
			System.out.println("SalesOrder not created");
			throw new Exception ("SalesOrder is not inserted correct");
		}
		return(rc);
	}

	private void insertSalesList(ArrayList<SalesLine> slList, int orderId) throws Exception {
		for(SalesLine sl : slList)
		{
			salesLineDb.insertSalesLine(sl, orderId);
		}
	}

	@Override
	public int updateSalesOrder(SalesOrder so) 
	{
		SalesOrder soObj  = so;
		int rc=-1;
		String query="UPDATE SalesOrder SET "+
				"creationDate ='"+ soObj.getCreationDate() + "', " +
				"paymentDate ='"+ soObj.getPaymentDate() + "', " +
				"total ='"+ soObj.getAmount() + "', " +
				"custId ='"+ soObj.getC().getId() + "', " +
				"deliveryId ='"+ soObj.getD().getDeliveryId() + "', " +
				"status ='"+ soObj.getStatus() + "', " +
				"discount ='"+ soObj.getDiscount() +
				" WHERE id = '"+ soObj.getOrderId() + "'";
		System.out.println("Update query:" + query);
		try{ // update employee
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);

			stmt.close();
			updateSalesLine(soObj.getSl(), soObj.getOrderId());
		}//end try
		catch(Exception ex){
			System.out.println("Update exception in SalesOrder db: "+ex);
		}
		return(rc);
	}

	private void updateSalesLine(ArrayList<SalesLine> slList, int orderId) throws Exception {
		salesLineDb.deleteSalesLine(orderId);
		for(SalesLine sl : slList)
		{
			salesLineDb.insertSalesLine(sl, orderId);
		}
	}

	@Override
	public int deleteSalesOrder(SalesOrder so) 
	{
		int rc=-1;

		String query="DELETE FROM SalesOrder WHERE id = '" +
				so.getOrderId() + "'";
		System.out.println(query);
		try{ // delete from salesOrder
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
			salesLineDb.deleteSalesLine(so.getOrderId());
		}//end try	
		catch(Exception ex){
			System.out.println("Delete exception in SalesOrder db: "+ex);
		}
		return(rc);
	}

	private ArrayList<SalesOrder> miscWhere(String wClause)
	{
		ResultSet results;
		ArrayList<SalesOrder> list = new ArrayList<SalesOrder>();	

		String query =  buildQuery(wClause);

		try{ // read the salesOrder from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);


			while( results.next() ){
				SalesOrder soObj = new SalesOrder();
				soObj = buildSalesOrder(results);	
				list.add(soObj);	
			}//end while
			stmt.close();  

		}//end try	
		catch(Exception e){
			System.out.println("Query exception - select: "+e);
			e.printStackTrace();
		}
		return list;
	}
	
	private SalesOrder singleWhere(String wClause)
	{
		ResultSet results;
		SalesOrder soObj = new SalesOrder();

		String query =  buildQuery(wClause);
		System.out.println(query);
		try{ // read the salesOrder from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			if( results.next() ){
				soObj = buildSalesOrder(results);
				stmt.close();
			}
			else{ //no salesOrder was found
				soObj = null;
			}
		}//end try	
		catch(Exception e){
			System.out.println("Query exception: "+e);
		}
		return soObj;
	}
	
	private String buildQuery(String wClause)
	{
		String query="SELECT id, creationDate, paymentDate, total, custId, deliveryId FROM SalesOrder";

		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;

		return query;
	}
	
	private SalesOrder buildSalesOrder(ResultSet results)
	{   SalesOrder soObj = new SalesOrder();
	try{ // the columns from the table salesOrder are used
		soObj.setOrderId(results.getInt("id"));
		soObj.setCreationDate(results.getDate("creationDate"));
		soObj.setPaymentDate(results.getDate("paymentDate"));
		soObj.setAmount(results.getDouble("total"));
		soObj.setC(customerDb.getCustomerById(results.getInt("custId")));
		soObj.setD(deliveryDb.getDeliveryById(results.getInt("deliveryId")));
		soObj.setStatus(results.getString("status"));
		soObj.setSl(salesLineDb.getAllSalesLines(results.getInt("id")));
	}
	catch(Exception e)
	{
		System.out.println("error in building the PartOrder object");
	}
	return soObj;
	}
	
}
